﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using PermitsAndVisasLeadsSyncher.Service_Proxy;
using PermitsAndVisasLeadsSyncher.Helpers;
using PermitsAndVisasLeadsSyncher.Model;
using System.Xml;
using PermitsAndVisasLeadsSyncher.Helpers.Logging;
using PermitsAndVisasLeadsSyncher.Data_Layer;

namespace PermitsAndVisasLeadsSyncher.Data_Gateway
{
    internal class LeadGateway
    {
        ILogger logger = LeadsSyncherLogger.CreateLogger();
        int successfullLeadCount;
        public List<Lead> LeadLoadFromService(string from, string lastLeadDate)
        {
            List<Lead> leadsList = new List<Lead>();
            LeadServiceRequest leadServiceRequest = new LeadServiceRequest();
            XmlNodeList xmlNodeList2 = leadServiceRequest.GetLeads(from, lastLeadDate);
            foreach (XmlNode xmlNode in xmlNodeList2)
            {
                Lead lead = new Lead();
                if (xmlNode.Attributes.Count > 0)
                {
                    lead.Id = xmlNode.Attributes["id"].Value;
                    lead.Name = xmlNode.Attributes["name"].Value;
                    lead.Email = xmlNode.Attributes["email"].Value;
                    lead.MobileNumber = xmlNode.Attributes["mobno"].Value;
                    lead.PhoneNumber = xmlNode.Attributes["phone"].Value;
                    lead.Message = xmlNode.Attributes["message"].Value;                   
                    lead.DateOfBirth = xmlNode.Attributes["dob"].Value;
                    lead.Nationality  = xmlNode.Attributes["nationality"].Value;
                    //lead.Residence = xmlNode.Attributes["nationality"].Value;
                    lead.Education = xmlNode.Attributes["education"].Value;
                    lead.VisaType = xmlNode.Attributes["visatype"].Value;
                    lead.Occupation = xmlNode.Attributes["occupation"].Value;
                    lead.NoOfExperience = xmlNode.Attributes["noofexp"].Value;
                    lead.SourceCountry = xmlNode.Attributes["src_country"].Value;
                    lead.Residence = xmlNode.Attributes["src_country"].Value;
                    lead.DestinationCountry = xmlNode.Attributes["dest_country"].Value;
                    lead.LeadSource = xmlNode.Attributes["leadsrc"].Value;
                    lead.EnteredDate = xmlNode.Attributes["edate"].Value;
                }
                leadsList.Add(lead);
            }
            MassageLeadsData(leadsList);
            return leadsList;
        }

        private void MassageLeadsData(List<Lead> leadsList)
        {
            foreach (Lead lead in leadsList)
            {
                lead.Name = lead.Name.Replace("'", "''");
                lead.Occupation = lead.Occupation.Replace("'", "''");
                lead.Message = lead.Message.Replace("'", "''");
                lead.Education = lead.Education.Replace("'", "''");
                lead.PhoneNumber = lead.PhoneNumber.Replace("'", "''");
            }
        }

        public bool InsertLeadIntoCRMDatabase(Lead lead, List<Country> countries, List<ProductInfo> products)
        {
            bool flag = false;
            SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnstring"].ConnectionString);
            SqlTransaction sqlTransaction = (SqlTransaction)null;
            
            try
            {
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
                SqlCommand command = sqlConnection.CreateCommand();
                command.Transaction = sqlTransaction;
                EducationGateway educationgateway = new EducationGateway();
                List<Education> educationList = educationgateway.FillEducationList(command);
                CheckTheLeadsQualificationAndUpdateDatabaseIfNeeded(educationList, lead, command);
                lead.sorceCountryID = InfoProvider.ReturnCountryId(countries, lead.SourceCountry);
                lead.ResidenceCountryId = InfoProvider.ReturnCountryId(countries, lead.Residence);
                lead.DestinationCountryId = InfoProvider.ReturnCountryId(countries, lead.DestinationCountry);
                string str2 = InfoProvider.AddPersonToCRMDataBaseAndReturnPersonId(QueryProvider.MakeSQLQueryToAddPerson(lead), command);
                lead.PersonID = str2;
                if (!string.IsNullOrEmpty(lead.PersonID))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddNewEventOfAddingPerson(lead), command);
                if (!string.IsNullOrEmpty(lead.Email.Trim()))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddEmailAddress(lead), command);
                if (!string.IsNullOrEmpty(lead.PhoneNumber.Trim()))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddPhoneNumber(lead), command);
                if (!string.IsNullOrEmpty(lead.MobileNumber.Trim()))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddMobileNumber(lead), command);
                string str3 = InfoProvider.SearchProductIdForCurrentlead(lead, products);
                lead.ProductId = str3;
                string str4 = InfoProvider.AddnewCasetoCRMDatabaseAndReturnCaseId(QueryProvider.MakeSQLQueryToAddNewCase(lead), command);
                lead.CaseId = str4;
                if (!string.IsNullOrEmpty(lead.CaseId))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddNewEventOfAddingCase(lead), command);
                if (!string.IsNullOrEmpty(lead.Message.Trim()))
                    DBHelper.ExecuteSqlQuery(QueryProvider.MakeSQLQueryToAddWebsiteComment(lead), command);
                flag = true;
                successfullLeadCount = successfullLeadCount + 1;
            }
            catch (SqlExecutionException ex)
            {
                logger.LogInfo("Inside LeadGateway.InserLeadIntoCRMDatabase");
                logger.LogInfo(string.Format("Lead having SQL execution Issue : LeadId {0}", lead.Id));
                logger.LogError(ex.OriginalException  , "  SQL Query : " + ex.SQLQuery );                
                sqlTransaction.Rollback();                
                sqlTransaction = (SqlTransaction)null;
            }
            catch (Exception ex1)
            {
                Console.WriteLine(ex1.Message);                
                logger.LogError(ex1, string.Empty);               
            }
            finally
            {
                if (sqlTransaction != null)
                    sqlTransaction.Commit();
                sqlConnection.Close();
            }
            
            return flag;
        }

        public void CheckTheLeadsQualificationAndUpdateDatabaseIfNeeded(List<Education> EducationList, Lead Leads, SqlCommand command)
        {
            bool flag = false;
            if (string.IsNullOrEmpty(Leads.Education))
                return;
            foreach (Education education in EducationList)
            {
                if (education.Name == Leads.Education)
                {
                    flag = true;
                    break;
                }
            }
            if (flag)
                return;
            string query=QueryProvider.MakeSQLQueryToUpdateEducation(Leads);
            command.CommandText = query ;
            try
            {                
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                logger.LogInfo("Function where caught exception : LeadGateway.CheckTheLeadsQualificationAndUpdateDatabaseIfNeeded");
                logger.LogError(ex, "SQL Query:" + query);
                throw;
            }
            
        }

        public void UpdateLeadsServiceInfoIntodatabase(string LastSucessfullEdate)
        {
            string query =string .Empty ;
            try
            {
                query= QueryProvider.MakeSQLQueryToAddLastSuccessfullLeadsDate(LastSucessfullEdate);
                DBHelper.ExecuteSqlQuery(query);
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "SQL query : " + query);
                throw;
            }
            //finally
            //{
           
            //}
        }

        public void LeadUpdation(List<Lead> leadlist)
        {
            string LastSucessfullEdate = string.Empty;
            CountryGateway countryGateway = new CountryGateway();
            List<Country> countries = countryGateway.FillListOfCountriesPresentInCRMDatabase();
            ProductGateway productGateway = new ProductGateway();
            List<ProductInfo> products = productGateway.FillListOfProductPresentInDatabase();
            
            foreach (Lead Leads in leadlist)
            {
                if (InsertLeadIntoCRMDatabase(Leads, countries, products))
                {
                    LastSucessfullEdate = Leads.EnteredDate;

                }
                
            }
            UpdateLeadsServiceInfoIntodatabase(LastSucessfullEdate);
            logger.LogInfo(string.Format("Successful Lead Count: {0}", successfullLeadCount));
            
        }
    }
}
